package com.poi.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.Column;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

//import com.poi.PinYinUtil;

/**
 * 类描述：Excel工具类 作者：郑朝军 on 2017/7/26 16:27 邮箱：1250393285@qq.com 公司：武汉智博创享科技有限公司
 * 修改人：郑朝军 on 2017/7/26 16:27 修改备注：
 */
public class ExcelUtil
{

    /**
     * 根据路径获取多少个Sheet页
     *
     * @param xlsPath
     * @return
     * @throws Exception
     */
    public static Sheet[] getSheet(String xlsPath) throws Exception
    {
        Sheet[] sheets = null;

        // 读取的文件
        File file = new File(xlsPath);
        FileInputStream input = new FileInputStream(file);
        Workbook wb = new XSSFWorkbook(input);

        // 获取表的总数
        int sheet_numbers = wb.getNumberOfSheets();
        sheets = new Sheet[sheet_numbers];
        for (int i = 0; i < sheet_numbers; i++)
        {
            // 遍历所有表
            Sheet sheet = wb.getSheetAt(i);
            sheets[i] = sheet;
        }
        wb.close();

        return sheets;
    }

    /**
     * 获取头部
     *
     * @param sheet
     * @return
     */
    public static String[] getHeader(Object sheet)
    {
        Sheet sht = (Sheet) sheet;
        int rows_num = sht.getLastRowNum();
        if (rows_num > 0)
        {
            Row row = sht.getRow(0);
            if (row != null && !(row.equals("")))
            {
                // 获取列数
                int columns_num = row.getLastCellNum();

                String[] s = new String[columns_num];
                for (int columns = 0; columns < columns_num; columns++)
                {
                    Cell cell = row.getCell(columns);
                    s[columns] = (String) getCell(cell);
                }
                return s;
            }
        }
        return null;
    }

    public static ArrayList<HashMap<String, String>> getVal(Object sheet)
    {
        ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
        Sheet sht = (Sheet) sheet;
        int rows_num = sht.getLastRowNum();
        String[] cols = getHeader(sht);
        for (int rows = 1; rows <= rows_num; rows++)
        {
            // 取得某一行对象
            Row row = sht.getRow(rows);
            if (row != null && !(row.equals("")))
            {
                // 获取列数
                int columns_num = row.getLastCellNum();
                HashMap<String, String> map = new HashMap<String, String>();
                for (int columns = 0; columns < columns_num; columns++)
                {
                    Cell cell = row.getCell(columns);
                    map.put(cols[columns], getCell(cell));
                }
                list.add(map);
            }
        }
        return list;
    }

    public static ArrayList<HashMap<String, String>> getVals(Object sheet)
    {


        ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
        Sheet sht = (Sheet) sheet;
        int rows_num = sht.getLastRowNum();
        String[] cols = getHeader(sht);
        List col = new ArrayList();
        List len = new ArrayList();
        List type = new ArrayList();


        // 打印表名


        for (int rows = 1; rows <= rows_num; rows++)
        {
            // 取得某一行对象

            Row row = sht.getRow(rows);

            if (row != null && !(row.equals("")))
            {
                // 获取列数
                int columns_num = row.getLastCellNum();
                HashMap<String, String> map = new HashMap<String, String>();

                // 打印create table
                for (int columns = 0; columns < 1; columns++)
                {
                    Cell cell = row.getCell(columns);
                    String cellvalue = getCell(cell);

                    Cell cell2 = row.getCell(6);
                    String namec = getCell(cell2);

                    if (!cellvalue.equals(""))
                    {
                        col.add(cellvalue);
                        toFld((String) col.get(0), cellvalue, col.indexOf(cellvalue), namec);
                    }
                }

                //  打印create table的 type   确定是哪种类型
                Cell cell2 = row.getCell(1);
                String cellvalue2 = getCell(cell2);
                if (!cellvalue2.equals(""))
                {
                    type.add(cellvalue2);
                }


                //  打印create table的 len   第三列
                Cell cell = row.getCell(2);
                String cellvalue = getCell(cell);
                if (!cellvalue.equals(""))
                {
                    len.add(cellvalue);
                }


                list.add(map);
            }
        }


        // 转table-cols
        StringBuffer stringBuffer = new StringBuffer();
        String tabname = (String) col.remove(0);

        for (int i = 0; i < col.size(); i++)
        {
            try
            {
                int Ibyte = (int) Double.parseDouble(len.get(i).toString());
                // 判断是什么类型
                String var = type.get(i).toString();
                switch (var)
                {
                    case "float8":
                        var = "number(" + (Ibyte == 0 ? "64" : Ibyte) + "),\n";
                        break;
                    case "timestamp":
                        var = " DATE,\n";
                        break;
                    default:
                        var = "       VARCHAR2(" + (Ibyte == 0 ? "64" : Ibyte) + " BYTE),\n";
                        break;
                }
                stringBuffer.append("  " + col.get(i) + var);
            }
            catch (Exception e)
            {
                e.printStackTrace();
                stringBuffer.append("  " + col.get(i) + "       VARCHAR2(64 BYTE),\n");
            }

        }

        String cols2 = stringBuffer.append("  constraint PK_" + tabname + " primary key (ID)\n").toString();

        toTab(tabname, cols2 + ");");
        return list;
    }

    public static ArrayList<HashMap<String, String>> getValsCustom(Object sheet)
    {
        ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
        Sheet sht = (Sheet) sheet;
        int rows_num = sht.getLastRowNum();
        String[] cols = getHeader(sht);
        for (int rows = 1; rows <= rows_num; rows++)
        {
            // 取得某一行对象
            Row row = sht.getRow(rows);
            if (row != null && !(row.equals("")))
            {
                // 获取列数
                int columns_num = row.getLastCellNum();
                HashMap<String, String> map = new HashMap<String, String>();
                for (int columns = 2; columns < columns_num; columns++)
                {
                    Cell cell = row.getCell(columns);
                    String cellvalue = getCell(cell);
                    if (!cellvalue.equals(""))
                    {
                        toFldValue(cell, cellvalue, columns);
                    }
                }
                list.add(map);
            }
        }
        return list;
    }


    /**
     * 转建表语句
     *
     * @param tabname
     * @param col
     */
    public static void toTab(String tabname, String col)
    {
        System.out.println("");
        System.out.println("");
        System.out.println("--" + tabname + "表");
        System.out.println("drop table " + tabname + ";");
        System.out.println("drop sequence seq" + tabname + ";");
        System.out.println("create sequence seq" + tabname + " increment by 1 start with 1 minvalue 1;");
        System.out.println("create table " + tabname +
                "(\n" +
                "  id                 number(9),\n" + col);


        System.out.println("delete from entity where name='" + tabname + "';");
        System.out.println("insert into entity (NAME,NAMEE,NAMEC,TYPE,MAJOR,MINOR,DOMAIN,TABGRP,TABATT,GLMAJ,GLMIN,FUNC,LOG,FORM) values ('" + tabname + "', '" + tabname + "', '表中文名', 1, 10, 300, 1, null, '" + tabname + "', 0, 0, null, 0, null);");

    }

    /**
     * 转fld脚本
     *
     * @param cellvalue
     */
    private static void toFld(String tabname, String cellvalue, int index, String namec)
    {
        if (index != 0)
        {
            System.out
                    .print("INSERT INTO fld(SYS, TABNAME, COLNAME, NAMEC, NAMEE, ISNUM, DISPORDER, DISPTYPE, NEWEDIT, EDITABLE, NULLABLE, DISPROWS, MINVAL, MAXVAL, DEFVAL, MINLEN, MAXLEN, GROUPE, GROUPC, QRYLEVEL, BZFLD, ISMCARD, PATTERN) VALUES ('0', ");
            System.out.println("'" + tabname + "', " + "'" + cellvalue + "', '" + ((namec == null || namec.equals("")) ? "暂无数据" : namec) + "', '" + cellvalue + "', '0', '" + index + "', '1', '1', '1', '1', '1', NULL, NULL, NULL, '0', '0', NULL, NULL, '0', '0', '0', NULL);");
        }
        else
        {
            System.out.println("delete from fld where TABNAME = '" + tabname + "';");
        }
    }

    /**
     * 转fldvalue脚本
     *
     * @param cellvalue
     * @param index
     */
    private static void toFldValue(Cell cell, String cellvalue, int index)
    {
        System.out
                .print("insert into fldvalue (sys, tabname, colname, disporder, dbvalue, dispc, dispe) values(0, '你的表名',");
        // System.out
        // .println("'"+PinYinUtil.toFirstCharLowCase(cellvalue)+"', "+index+", '"+index+"', '"+cellvalue+"', '"+cellvalue+"');");
        try
        {
//			System.out.println("'你的列字段', " + index + ", '" + index + "', '"
//					+ cellvalue + "', '"
//					+ PinYinUtil.toFirstCharLowCase(cellvalue) + "');");
        }
        catch (Exception e)
        {
//			e.printStackTrace();
            System.out.println("'你的列字段', " + index + ", '" + index + "', '"
                    + cellvalue + "', '" + cellvalue + "');");
        }
    }

    public static String getCell(Object cell)
    {
        if (cell != null)
        {
            Cell cel = (Cell) cell;
            switch (cel.getCellType())
            {
                case Cell.CELL_TYPE_STRING: // 字符串
                {
                    String value = cel.getStringCellValue();
                    if (value == null)
                    {
                        value = "";
                    }
                    return value;
                }
                case Cell.CELL_TYPE_NUMERIC: // 数字
                {
                    if (HSSFDateUtil.isCellDateFormatted(cel))
                    {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        return sdf
                                .format(HSSFDateUtil.getJavaDate(cel
                                        .getNumericCellValue())).toString();
                    }
                    return String.valueOf(cel.getNumericCellValue());
                }
                case Cell.CELL_TYPE_BLANK: // 空值
                {
                    String value = "";
                    return value;
                }
            }
        }
        return "";
    }

    public static void main(String[] args)
    {

//		String path = "D:\\work_cx\\Android\\code\\bjxm\\核污染北江项目\\核污染\\核污染.xlsx";
        String path = "D:\\work_cx\\Android\\code\\bjxm\\核污染北江项目\\核污染\\核污染 - 副本.xlsx";
        try
        {
            Sheet[] sheets = ExcelUtil.getSheet(path);
//			 for (int i = 8; i < 9; i++) {
//			 // 遍历所有表
//			 getVals(sheets[i]);
//			 }

//			for (int i = 1; i < 2; i++) {
//				// 遍历所有表
//				getValsCustom(sheets[i]);
//				System.out
//						.println("============================================================");
//			}


            // 显示sheet0页面的分类
//			 getValsCustom(sheets[1]);
            getVals(sheets[8]);
        }
        catch (Exception e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}
